package com.bidevalution.dao.jdbc;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.CallableStatementCallback;
import org.springframework.jdbc.core.CallableStatementCreator;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.stereotype.Component;

import com.bidevalution.support.exception.BizException;

@Component
public class JdbcTemplateSupport {

	private final Logger logger = LoggerFactory.getLogger(JdbcTemplateSupport.class);

	@Autowired
	private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

	public List<Map<String, Object>> selectForList(String sql, Map<String, Object> paramMap) {
		try {
			logger.info(sql);
			logger.info(paramMap + "");
			return namedParameterJdbcTemplate.queryForList(sql, paramMap);
		} catch (Exception e) {
			logger.error(e.getMessage());
			return null;
		}

	}
	public void save(String sql, SqlParameterSource sqlParameterSource) {
		try {
			logger.info(sql);
			namedParameterJdbcTemplate.update(sql, sqlParameterSource);
		} catch (Exception e) {
			logger.error(e.getMessage());
		}
	}
	public void insert(String sql, Map<String, Object> paramMap) {
		try {
			logger.info(sql);
			logger.info(paramMap + "");
			namedParameterJdbcTemplate.update(sql, paramMap);
		} catch (Exception e) {
			logger.error(e.getMessage());
		}
	}

	public void bathInsert(String sql, SqlParameterSource[] batchArgs) {
		try {
			logger.info(sql);
			logger.info(batchArgs + "");
			namedParameterJdbcTemplate.batchUpdate(sql, batchArgs);
		} catch (Exception e) {
			logger.error(e.getMessage());
		}
	}

	public void bathUpdate(String sql, SqlParameterSource[] batchArgs) {
		try {
			logger.info(sql);
			logger.info(batchArgs + "");
			namedParameterJdbcTemplate.batchUpdate(sql, batchArgs);
		} catch (Exception e) {
			logger.error(e.getMessage());
		}
	}

	public void update(String sql, Map<String, Object> paramMap) {
		try {
			logger.info(sql);
			logger.info(paramMap + "");
			namedParameterJdbcTemplate.update(sql, paramMap);
		} catch (Exception e) {
			logger.error(e.getMessage());
		}
	}

	public <T> T selectForObject(String sql, Map<String, ?> paramMap, Class<T> requiredType) {
		try {
			logger.info(sql);
			logger.info(paramMap + "");
			return namedParameterJdbcTemplate.queryForObject(sql, paramMap, requiredType);
		} catch (Exception e) {
			return null;
		}
	}

	public <T> T selectForObject(String sql, Map<String, ?> paramMap, RowMapper<T> rowMapper) {
		logger.info(sql);
		logger.info(paramMap + "");
		try {
			return namedParameterJdbcTemplate.queryForObject(sql, paramMap, rowMapper);
		} catch (EmptyResultDataAccessException e) {
			logger.error(e.getMessage());
			return null;
		}
	}
	public Boolean callProduce(String procedureName, Object... values) throws Exception{
		logger.info("执行存储过程:"+procedureName);
		return namedParameterJdbcTemplate.getJdbcTemplate().execute(new CallableStatementCreator() {
			CallableStatement cs;

			@Override
			public CallableStatement createCallableStatement(Connection con) throws SQLException {
				StringBuilder sBuilder = new StringBuilder();
				sBuilder.append("{call " + procedureName + "(");
				int size = values == null ? 0 : values.length;
				String storedProc = "";
				if (size > 0) {
					for (int i = 1; i <= size; i++) {
						sBuilder.append("?").append(",");
					}
				}
				storedProc = sBuilder.append("?").append(")}").toString();
				// 调用的sql
				cs = con.prepareCall(storedProc);
				if (size > 0) {
					for (int i = 0; i < size; i++) {
						cs.setObject(i + 1, values[i]);// 设置输入参数的值
					}
				}
				cs.registerOutParameter(size + 1, java.sql.Types.VARCHAR);// 注册输出参数 返回类型
				return cs;
			}
		}, new CallableStatementCallback<Boolean>() {
			public Boolean doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
				try {
					cs.execute();
					String result = cs.getString(values.length + 1);
					logger.info(procedureName+"，执行结果:"+result);
					if(result == null ||"1".equals(result)||"success".equals(result)) {
						return true;
					}else if("0".equals(result)||"fail".equals(result)) {
						return false;
					}else {
						throw new SQLException(result,null,10000);
					}
				
				} catch (Exception e) {
					logger.error(procedureName+">>>>>>>>>>>>>>>>>>>"+e.getMessage(), e);
					throw new BizException(e.getMessage(),e);
				} finally {
					if (cs != null) {
						cs.close();
					}
				}
			}
		});
	}

	public Map<String, Object> selectForMap(String sql, Map<String, Object> paramMap) {
		logger.info(sql);
		logger.info(paramMap + "");
		try {
			return namedParameterJdbcTemplate.queryForMap(sql, paramMap);
		} catch (Exception e) {
			logger.error(e.getMessage());
			return null;
		}

	}

	public <T> List<T> selectForList(String sql, Map<String, Object> paramMap, Class<T> requiredType) {
		logger.info(sql);
		logger.info(paramMap + "");
		try {
			return namedParameterJdbcTemplate.queryForList(sql, paramMap, requiredType);
		} catch (Exception e) {
			logger.error(e.getMessage());
			return null;
		}
	}

	public <T> List<T> selectForList(String sql, Map<String, Object> paramMap, RowMapper<T> rowMapper) {
		logger.info(sql);
		logger.info(paramMap + "");
		try {
			return namedParameterJdbcTemplate.query(sql, paramMap, rowMapper);
		} catch (Exception e) {
			logger.error(e.getMessage());
			return null;
		}
	}
}
